#!/user/bin/env python
# -*-coding:utf-8-*-
'''
@author: Patty
@project: automation_framework
@file: std.py
@time: 2019/10/30  16:13
@IDE: PyCharm 
'''
import traceback,requests,time
from jsonpath import jsonpath
from decimal import Decimal
import base.globalvars as glo
from proj_spec.DAS.biz import get_biz_std_table_name
from proj_spec.DAS.model import get_model_info
from proj_spec.DAS.element import get_element_mapped_biz
from proj_spec.DAS.misc import notify
from proj_spec.DAS.std_def import get_std_fin_item_info
from proj_spec.DAS.std_col import get_std_col_infos, get_std_col_info
from util.db_util import execute_query
from util.date_util import str_to_timestamp
from util.number_util import get_number_from_string
import logging,re


def __init():
    from base.get_config import get_header_file
    from base.getdata import get_header
    global url1,url2,url3,header1,header2
    url1 = glo.get_value('url1')
    url2 = glo.get_value('url2')
    url3 = glo.get_value('url3')
    header1 = get_header(get_header_file(), "header1")
    header2 = get_header(get_header_file(), "header2")



def validate_std_data_trace(file_id,model_id,element_id,start_time_str=None):
    """验证标准化数据追踪

    :param file_id: 文件id
    :param model_id: 模型id
    :param element_id: 元素id

    :return: 成功：True， 失败：False
    """
    # Todo: 验证标准化字段映射关系
    #return True #披露数据合并单元格问题待解决
    logging.info("标准化数据追踪：file %s,model %s,element %s"%(file_id,model_id,element_id))
    from base.getdata import get_header
    from base.get_config import get_header_file

    root_url2 = glo.get_value("url2")
    root_url3 = glo.get_value("url3")
    header1 = get_header(get_header_file(), "header1")
    source_url = root_url2 + "/api/v1/ex/ExtractDataEx/get-dynamic-table?FileId=%s&ModelId=%s&ElementId=%s"%(file_id,model_id,element_id)
    source_res = requests.get(source_url,headers = header1)
    if source_res.status_code==204:
        logging.warning("未找到披露数据")
    elif source_res.status_code!=200:
        logging.error("获取披露数据表失败,%s"%source_res.text)
        return False

    if len(jsonpath(source_res.json(),"$.tables"))>1:
        logging.warning("披露数据包含多张表")
    unit = jsonpath(source_res.json(),"$.tables[0].unit")
    unit_mul_dict={"千元":1000,"百元":100,"万元":10000,"百万":1000000,"百万元":1000000,"千万":10000000,"千万元":10000000}
    try:
        multiplier=unit_mul_dict[unit[0]]
    except Exception as e:
        #logging.debug("key error")
        multiplier=1


    std_url = root_url3 + "/api/v1/das/stddata?FileId=%s&ModelId=%s&ElementId=%s" % (file_id, model_id, element_id)
    std_res = requests.get(std_url, headers=header1)
    if std_res.status_code!=200:
        logging.error("获取标准化表格失败,%s"%std_res.text)
        return False

    std_res_json = std_res.json()

    # DAS-2774 标准化追踪表增加upt
    # 审核通过元素不会重新标准化，将start_time设置为None
    if is_std_review_passed(file_id,model_id,element_id):
        start_time_str = None
    upt = jsonpath(std_res_json,"$.upt")
    if start_time_str is not None: #DAS-4552 标准化数据追踪接口upt使用trace表更新时间
        timezone_offset = glo.get_value("timezone_offset")
        assert upt is not False and len(upt) > 0 and str_to_timestamp(start_time_str,'%Y-%m-%d %H:%M:%S') <= \
               str_to_timestamp(upt[0],'%Y-%m-%dT%H:%M:%S.000Z') + float(timezone_offset) * 60*60,\
            "文件id %s 元素id %s upt小于start_time：start_time_str %s,upt %s,timezone_offset %s"%(file_id, element_id, start_time_str, upt[0],timezone_offset)
    else: #仅验证upt字段有值
        assert upt is not False and len(upt) > 0 and upt[0] is not None,"标准化数据追踪接口upt字段为空：file_id %s,model_id %s,element_id %s,start_time_str %s"\
                                                                        %(file_id,model_id,element_id,start_time_str)
    assert jsonpath(std_res_json, "$.upu") is not False and jsonpath(std_res_json, "$.upu") != "", "标准化数据追踪接口upu为空: " \
                                                                                                "file_id %s,model_id %s,element_id %s,start_time_str %s"%(file_id,model_id,element_id,start_time_str)
    if jsonpath(std_res_json,"$.cellData")==[[]]:
        logging.error("标准化数据为空")
        return False

    # DAS-5681 容错到同一标准化科目时应做累加计算，因此标准化数据中不应有重复的标准化科目
    # Todo: DAS-6480 修改了需求，通过配置决定C表是否累加
    fin_item_names = jsonpath(std_res_json,"$.cellData[*][0].cellValue")
    assert len(fin_item_names)==len(set(fin_item_names))

    # Todo: 验证/das/stddata?FileId=227545&ModelId=&ElementId=接口返回数据本身的正确性（非数据追踪正确性）：
    # 当前只有标准化a，b表有重述
    client_code,biz_code = get_element_mapped_biz(element_id)
    std_table_name = get_biz_std_table_name(client_code, biz_code)
    if std_table_name in ['t_das_std_data','t_das_std_data_b']:
        restate_col_disp_names = get_restate_col_display_names(std_table_name)  # 重述类型字段显示名称列表
        header_names = jsonpath(std_res_json, "$.headerNames")
        if header_names is not False:
            header_names = header_names[0]
        restate_col_indexes = [header_names.index(display_name) for display_name in restate_col_disp_names]
    else:
        restate_col_indexes = []

    for row in jsonpath(std_res_json,"$.cellData[*]"):
        # 获取披露科目对应的标准化科目的类型
        try:
            std_fin_item_namecode = row[0]['cellValue']
            pattern = ".+\((\w+)\)$"
            code = re.match(pattern,std_fin_item_namecode).group(1)
            fin_item_type = get_std_fin_item_info("item_type", std_code=code)
            if fin_item_type==2:
                row_multiplier=1 #比率项目不做倍乘
            else:
                row_multiplier=multiplier
        except Exception as e:
            logging.error("Exception: 获取标准化科目名称代码失败：%s"%e)
            traceback.print_exc()
            return False


        #cells = [cell for cell in row if cell['cellId'] is not None and cell['cellId']!=[]]
        # 重述列不需要验证单元格追踪
        # cells = [cell for cell in row if 'cellId' in cell.keys() and cell['cellId'] != [] and cell['cellId'] is not None]
        cells = [row[i] for i in range(len(row)) if 'cellId' in row[i].keys() and row[i]['cellId'] != [] and row[i][
            'cellId'] is not None and i not in restate_col_indexes]

        for cell in cells:  #标准化数据

            # 不是按规则计算的
            if 'formula' not in cell.keys():
                matched_cells = jsonpath(source_res.json(),"$.tables[*].cells[?(set(@.ids).intersection(set(%s))!=set())]"%cell['cellId'])
                assert matched_cells is not False, "标准化数据单元格没有对应的披露数据：file_id %s model_id %s element_id %s%s" \
                                                   % (file_id, model_id, element_id, cell['cellId'])
            # if 'cellValue' in cell.keys() and (cell['cellValue']=='' or cell['cellValue'] is None):
            #     cell['cellValue'] = '0'
                try:
                    if 'cellValue' in cell.keys() and cell['cellValue'] is not None and re.match("^[\d(-][,.\d()-]+[\d)]$",cell['cellValue']): #匹配金额

                        matched_values=[data_process(mc['value']) if mc['value'] is not None and mc['value'].strip() not in ['', '-', '－', '不适用'] else 0 for mc in matched_cells]
                        # 披露数据小于两位小数的，保留两位小数，大于两位的，按披露数据小数位数（至多6位）
                        digit_len = max(len(str(value).split(".")[1]) if len(str(value).split("."))>1 else 0 for value in matched_values)
                        if digit_len<2:
                            digit_len=2
                        elif digit_len>6:
                            digit_len=6


                        format_str = '%df'%digit_len
                        format_str = '%.'+format_str

                        decimal_format='0.'+digit_len*'0'
                        if 'cellValue' in cell.keys() and len(str(cell['cellValue']).split("."))>1 and len(str(cell['cellValue']).split(".")[1])!=digit_len:
                            logging.error("标准化数据精度和披露数据不一致：cellId:%s, 披露数据:%s,标准化数据:%s" % (cell['cellId'], matched_values, cell['cellValue']))
                            return False

                        elif 'cellValue' in cell.keys() and cell['cellValue'] != '-' and str(Decimal(str(sum(matched_values))).
                                quantize(Decimal(decimal_format))*row_multiplier) != format_str % float(cell['cellValue'].replace(',', '')):  # sum([1,364,229.78,2,599,955.54])结果会是3,964,185.320003
                            logging.error("%s数据不匹配：披露数据:%s,单位：%s,标准化数据:%s" % (cell['cellId'], matched_values, unit,cell['cellValue']))
                            return False

                    elif 'cellValue' in cell.keys() and cell['cellValue'] is not None:#当前百分比当字符串处理
                        matched_values = [mc['value'] for mc in matched_cells]
                        assert len(matched_values)==1,"非金额型标准科目不应对应多于一个披露数据：cellId:%s, 披露数据:%s,标准化数据:%s"% (cell['cellId'], matched_values, cell['cellValue'])

                        if cell['cellValue'] is None or cell['cellValue']=='':
                            assert matched_values[0] is None or matched_values[0]==''
                        else:
                            assert cell['cellValue']==matched_values[0]
                except Exception as e:
                    logging.error("cell Id:%s cell Value: %s %s"%(cell['cellId'],cell['cellValue'],e))
                    traceback.print_exc()
                    return False

    return True

def data_process(input_str):
    """对输入的单元格值做格式处理，如带括号的数字转换为负数、去除中间的空格（披露的PDF中格式问题）

    :param input_str: 待处理的单元格值

    :return: 处理后的值，如有异常则返回输入值
    """

    #输入值中可能存在因跨页提取等存在的limitation造成的提取错误
    # 如华泰证券2018Q4 合并资产负债表P107-108,资产总计本公司上期值提取为276,731,164,518.84(金额单位：人民币元)，需要去除文本部分
    try:
        input_str = input_str.replace(',','')
        input_str = input_str.replace(' ', '')
        #matched = re.match('(-)?\(?(\d.+)\)?.*',input_str)
        matched = re.match('(-)?\s*(\()?\s*([\d.]+)\s*(\))?.*', input_str)
        if matched is not None: #添加符号
            if matched.group(1) is not None or matched.group(2) is not None: #有负号或表示负数的括号
                #input_str = matched.group(2)
                input_str = '-' + matched.group(3)
            else:
                input_str = matched.group(3)
        return float(input_str)
    except Exception as e:
        logging.error(e)
        traceback.print_exc()
        return input_str

def _get_file_element_restate_setting(file_id,element_id):
    """获取文件元素上的重述设置，若没有设置则默认1 需要重述

    :param file_id: 文件id
    :param element_id: 元素id

    :return: 文件元素的重述设置：0 不需要重述 1 需要重述
    """
    sql = "select need_restate from t_das_restate_element where file_id=%s and element_id=%s"%(file_id,element_id)
    res = execute_query(sql)
    if len(res)>0:
        return res[0][0]
    else:
        return 1



# def get_element_id_from_std_data(vfile_id, fin_item_code):
#     """从标准化数据表中获取元素id
#
#     :param vfile_id:
#     :param fin_item_code:
#     :return:
#     """
#     # 不同业务元素下同名科目也应建不同的科目code，所以正式环境上根据科目code查找到的元素应该是唯一的；但是测试环境会有多个模型下的不同元素有相同的科目
#     # 由于线上无此问题, 因此系统不予考虑。目前按仅有一个元素的情况处理，考虑后面移除业务关联多个模型的情况
#     sql = "select element_id from t_das_std_data where vfile_id=%s and std_item_code='%s' and state=0 order by upt desc" % (
#     vfile_id, fin_item_code)
#     res = execute_query(sql)
#     if len(res) > 0:
#         element_id = res[0][0]
#         # 查询文件元素重述设置
#     else:
#         return None


def get_std_element_id(vfile_id, fin_item_code, biz_code, std_table_name):
    """获取标准化科目对应的元素id（QA环境一个业务对应多个元素，查找标准化值时需定位到对应的元素）

    :param biz_code:
    :param vfile_id:
    :param fin_item_code: 标准化科目值
    :param std_table_name: 标准化表名

    :return: 对应的元素id
    """

    from proj_spec.DAS.biz import get_std_biz_element_info

    try:
        biz_element_info = get_std_biz_element_info(biz_code)
        biz_related_element_ids = [info['element_id'] for info in biz_element_info]
        related_elements_str  = ','.join([str(id) for id in biz_related_element_ids])
        sql = "select element_id from %s where vfile_id=%s and std_item_code='%s' and state=0 and element_id in (%s) order by upt desc" % (
                                                                std_table_name, vfile_id, fin_item_code, related_elements_str)
        res = execute_query(sql)
        if len(res) > 0:
            return  res[0][0]
        else:
            return -1
    except Exception as e:
        logging.error("get_std_element_id fail: %s"%e)
        return -1


def get_file_element_need_restate(vfile_id, fin_item_code, biz_code, std_table_name):
    """获取文件上和fin_item_code对应的元素是否重述的信息

    :param biz_code:
    :param vfile_id:
    :param fin_item_code:

    :return: 需要取重述值则返回True， 否则返回False
    """
    from proj_spec.DAS.File import get_file_id_from_vfile  #避免循环导入

    element_id = get_std_element_id(vfile_id, fin_item_code,biz_code, std_table_name)

    if element_id!=-1:
        # 查询文件元素重述设置
        file_id = get_file_id_from_vfile(vfile_id)
        need_restate = _get_file_element_restate_setting(file_id, element_id)
        if need_restate == 0:
            return False
        else:
            return True
    else: #未找到对应元素
        return None


def get_item_std_data(vfile_id, fin_item_code, biz_code, restate=False):
    """从a表中获取标准化值, 默认情况为有重述值则取重述值

    :param biz_code:
    :param vfile_id: 文件的vfile_id
    :param fin_item_code: 标准化科目编码
    :biz_code: 业务编码，用于查找关联的元素列表
    :param restate: 是否取重述值

    :return: 标准化值
    """


    # 科目容错时，若目标科目所在科目分组和本元素对应业务所在分组不同，会造成使用映射关系中biz_code查不到对应标准化数据,因此映射关系的biz_code不作为条件
    # 如果文件应用重述，需进一步确认元素是否应用重述。
    if restate is True:
        # 不同业务元素下同名科目也应建不同的科目code，所以正式环境上根据科目code查找到的元素应该是唯一的；但是测试环境会有多个模型下的不同元素有相同的科目
        # 由于线上无此问题, 因此系统不予考虑。目前按仅有一个元素的情况处理，考虑后面移除业务关联多个模型的情况

        need_restate = get_file_element_need_restate(vfile_id, fin_item_code, biz_code, "t_das_std_data")
        if need_restate is None:
            return None
        else:
            restate = need_restate
        # sql = "select element_id from t_das_std_data where vfile_id=%s and std_item_code='%s' and state=0 order by upt desc"%(vfile_id, fin_item_code)
        # res = execute_query(sql)
        # if len(res)>0:
        #     element_id = res[0][0]
        #     # 查询文件元素重述设置
        #     file_id = get_file_id_from_vfile(vfile_id)
        #     need_restate = _get_file_element_restate_setting(file_id, element_id)
        #     if need_restate == 0:
        #         restate = False
        # else:
        #     return None

    #element_id = get_std_element_id(vfile_id, fin_item_code,biz_code, "t_das_std_data")
    #根据输出字段查找标准化值
    #std_col_name_rv = get_std_col_info("t_das_std_data", std_field='rv', is_output=1)['std_col_name']
    output_col_id_rv = get_std_col_info("t_das_std_data", std_field='rv', is_output=1)['id']
    # sql_rv = "select rv,upt from t_das_std_data where vfile_id=%s and std_item_code='%s' and output_col_id=%s and state=0 and element_id=%s order by upt desc" \
    #     % (vfile_id, fin_item_code,output_col_id_rv,element_id)
    sql_rv = "select rv,upt from t_das_std_data where vfile_id=%s and std_item_code='%s' and output_col_id=%s and state=0 order by upt desc" \
        % (vfile_id, fin_item_code,output_col_id_rv)
    #std_col_name_fv = get_std_col_info("t_das_std_data", std_field='fv', is_output=0)['std_col_name']
    output_col_id_fv = get_std_col_info("t_das_std_data", std_field='fv', is_output=1)['id']
    # sql_fv = "select fv,upt from t_das_std_data where vfile_id=%s and std_item_code='%s' and output_col_id=%s and state=0 and element_id=%s order by upt desc" \
    #     % (vfile_id, fin_item_code,output_col_id_fv,element_id)
    sql_fv = "select fv,upt from t_das_std_data where vfile_id=%s and std_item_code='%s' and output_col_id=%s and state=0 order by upt desc" \
        % (vfile_id, fin_item_code,output_col_id_fv)

    # Todo: 改进？
    # DAS-3893 使用重述值开关打开情况下，有重述值使用重述值，无重述值使用原值
    try:
        if restate is True:
            res = execute_query(sql_rv)
            # DAS-5048 下期文件重述后没值，则重述值为Null
            if len(res)>0:
                return res[0]
            else:
                return None
            # if len(res)>0:
            #     return res[0]
            # else:
            #     res = execute_query(sql_fv)
            #     if len(res)>0:
            #         return res[0]
            #     else:
            #         return None
        else:
            res = execute_query(sql_fv)
            if len(res) > 0:
                return res[0]
            else:
                return None
    except Exception as e:
        logging.error("get_std_data_a: %s"%e)
        traceback.print_exc()
        return None


def get_item_std_data_b(vfile_id, fin_item_code, biz_code, item_detail, restate=False):
    """从b表获取标准化数据

    :param biz_code:
    :param vfile_id: 文件的vfile_id
    :param fin_item_code: 标准化科目编码
    :param item_detail: 传入节点的itemDetail字段值

    :return: 根据数据列不同，返回b表中不同的字段值
    """

    # field_name_dict = { "累计折旧和累计摊销|增加值": "amort_v_inc", "累计折旧和累计摊销|减少值": "amort_v_dec","累计折旧和累计摊销|期初余额": "amort_bv", "累计折旧和累计摊销|期末余额": "amort_ev",
    #                    "账面原值|增加值":"org_v_inc","账面原值|减少值":"org_v_dec","账面原值|期初余额": "org_bv", "账面原值|期末余额": "org_ev",
    #                     "减值准备|增加值": "rev_v_inc", "减值准备|减少值": "rev_v_dec", "减值准备|期初余额": "rev_bv", "减值准备|期末余额": "rev_ev",
    #                     "账面价值|增加值": "booking_v_inc", "账面价值|减少值": "booking_v_dec", "账面价值|期初余额": "booking_bv", "账面价值|期末余额": "booking_ev"
    #                    }
    # item_detail更新为'output_累计折旧和累计摊销增加值'格式
    # 通过在t_das_std_col表根据display_name查到col_id, 从而在t_das_std_data_b表中查到对应数据

    if restate is True:
        # 不同业务元素下同名科目也应建不同的科目code，所以正式环境上根据科目code查找到的元素应该是唯一的；但是测试环境会有多个模型下的不同元素有相同的科目
        # 由于线上无此问题, 因此系统不予考虑。目前按仅有一个元素的情况处理，考虑后面移除业务关联多个模型的情况

        need_restate = get_file_element_need_restate(vfile_id, fin_item_code, biz_code, "t_das_std_data_b")
        if need_restate is None:
            return None
        else:
            restate = need_restate

    #element_id = get_std_element_id(vfile_id, fin_item_code, biz_code, "t_das_std_data_b")
    if item_detail!='' and item_detail is not None:

        col_infos = get_std_col_infos(std_col_name=item_detail, tbl_name='t_das_std_data_b')
        if len(col_infos)>0:
            col_info = col_infos[0]
            col_id = col_info['id']
            std_field = col_info['std_field']
            std_col_name = col_info['std_col_name']

            if restate and '_ev' in std_field:  # B表重述只会有期末余额的重述
            #if restate and '_ev' in std_field and '_restate' in std_col_name: # B表重述只会有期末余额的重述
                std_field = std_field.split('_')[0]+'_rv'
                # sql = """SELECT %s,upt FROM t_das_std_data_b WHERE vfile_id=%s and std_item_code='%s' AND %s is not null and state=0 and element_id=%s order by upt desc
                #             """ % (std_field.replace('_ev','_rv'), vfile_id, fin_item_code, std_field, element_id)
                sql = """SELECT %s,upt FROM t_das_std_data_b WHERE vfile_id=%s and std_item_code='%s' AND %s is not null and state=0 order by upt desc
                                            """ % (std_field.replace('_ev', '_rv'), vfile_id, fin_item_code, std_field)
            elif restate:
                sql = """SELECT %s,upt FROM t_das_std_data_b WHERE 1=0""" % std_field # 其他字段重述情况下不取值
            else:
                # sql = """SELECT %s,upt FROM t_das_std_data_b WHERE vfile_id=%s and std_item_code='%s' AND output_col_id=%s and state=0 and element_id=%s order by upt desc
                # """ % (std_field, vfile_id, fin_item_code, col_id, element_id)
                sql = """SELECT %s,upt FROM t_das_std_data_b WHERE vfile_id=%s and std_item_code='%s' AND output_col_id=%s and state=0 order by upt desc
                """ % (std_field, vfile_id, fin_item_code, col_id)

            try:
                res = execute_query(sql)
                if len(res)>0:
                    return res[0]
                else: #存在单元格无标准值的情况
                    return None
            except Exception as e:
                logging.error(e)
                traceback.print_exc()
                return None
        else:
            return None
    else: #B表无明细则无值
        return None


def get_item_std_data_c(vfile_id, std_item_code, item_detail, restate=False):
    """从C表获取标准化数据

    :param vfile_id: 文件的vfile_id
    :param std_item_code: 标准化科目编码
    :param item_detail: 传入节点的itemDetail字段值
    :param restate: 是否应用重述值

    :return: 标准化数据
    """
    if restate is False:
        #item_detail为层级结构
        if item_detail is not None and item_detail!='':

            col_infos = get_std_col_infos(std_col_name=item_detail, tbl_name='t_das_std_data_c')
            if len(col_infos)>0:
                col_info = col_infos[0]

                col_id = col_info['id']
                std_field = col_info['std_field']


                # sql = "select %s from t_das_std_data_c where vfile_id=%s and std_item_code='%s' and std_col_code='%s' %s order by upt desc" \
                #       % ( std_field_name, vfile_id, std_item_code, splitted[-1],parent_col_sub)
                sql = """SELECT %s,upt FROM t_das_std_data_c WHERE vfile_id=%s and std_item_code='%s' AND output_col_id=%s and state=0 order by upt desc
                """%(std_field, vfile_id, std_item_code, col_id)
            else:
                return None

        else:
            #fin_item_name = get_std_fin_item_info("name",std_code='jn_kjs_swj_bc')
            sql = "select std_col_code from t_das_std_data_c where vfile_id=%s and std_item_code='%s' and state=0 order by upt desc"%(vfile_id, std_item_code)
            res = execute_query(sql)
            try:
                if len(res)>0:
                    std_col_code = res[0][0]
                else:
                    return None
            except Exception as e:
                logging.error("get std_col_code fail:%s"%e)
                return None

            std_field_name = get_std_col_info("t_das_std_data_c", std_col_name=std_col_code)['std_field']
            sql = "select %s,upt from t_das_std_data_c where vfile_id=%s and std_item_code='%s' and state=0 order by upt desc" \
                  % (std_field_name, vfile_id, std_item_code)
        try:
            res = execute_query(sql)
            if len(res) > 0:
                return res[0]
            else:  # 存在单元格无标准值的情况
                return None
        except Exception as e:
            logging.error("get_std_data_c:%s"%e)
            traceback.print_exc()
            return None
    else:
        #文件开启重述时，因c表均无重述值，则参与coa时取值均为空
        return None

def get_std_trace_data(file_id,std_item_code,std_tbl_name):
    """获取标准化追踪数据

    :param file_id: 文件id
    :param std_item_code: 标准化科目值
    :param std_tbl_name: 标准化表名

    :return: 标准化追踪数据
    """
    sql = """select t.id,t.col_id,t.src_id,t.file_id,t.element_id,t.table_id,t.model_id,t.upt,b.std_item_code,b.upt,b.model_id
            from t_das_std_data_trace t left join %s b on b.id=t.row_id where t.file_id=%s and b.std_item_code='%s' """\
          %(std_tbl_name,file_id,std_item_code)

    try:
        res = execute_query(sql)
        return res
    except Exception as e:
        logging.error("get_std_trace_data:%s"%e)
        traceback.print_exc()
        return []


def call_standardize_interface(file_id, model_id, element_ids):
    """调用客户端标准化接口

    :param file_id: 文件id
    :param model_id: 模型id
    :param element_id: 元素id
    :param stdForceMode: (obsolete)是否强制标准化，默认为False

    :return:
    """
    from base.getdata import get_header
    from base.get_config import get_header_file
    #root_url3 = glo.get_value("url3")
    root_url = glo.get_value("url4")
    header2 = get_header(get_header_file(), "header2")

    # payload = {
    #             "fileId":file_
    #             "modelId":model_id,
    #             "elementIds":element_ids,
    #             "stdForceMode":stdForceMode
    #             }

    qc_group = get_model_info(model_id=model_id)['qcgroup']
    payload = {
        "EventOwner": "das_client_maintain1",
        "FileId": file_id,
        "Model": {"ElementIds": element_ids, "ModelId": model_id, "QcGroup": qc_group}
    }

    res = requests.post(root_url + "/das/v1/workflow_event/clientfile",headers = header2, json=payload) # 标准化接口使用客户端发起标准化接口，便于在相关表中查找执行记录
    if res.status_code==200:
        return True
    else:
        logging.error("调用标准化接口失败 %s"%payload)
        return False


def is_std_review_passed(file_id, model_id, element_id):
    """判断文件元素是否已审核通过

    :param file_id: 文件id
    :param model_id: 模型id
    :param element_id: 元素id

    :return: 文件元素是否已审核通过
    """

    from base.getdata import get_header
    from base.get_config import get_header_file

    # root_url3 = glo.get_value("url3")
    root_url = glo.get_value("url1")
    header1 = get_header(get_header_file(), "header1")
    #
    # res = requests.get(root_url + "/api/v1/StdReviewStatus/review-fallback/%s/%s"%(file_id,model_id), headers = header1)
    # assert res.status_code==200,"/StdReviewStatus/review-fallback接口获取失败，%s"%res.text
    #
    # return element_id in jsonpath(res.json(),"$[*].elementId")

    url = root_url + "/api/v1/StdReviewStatus/filter?modelId=%s&elementIds=%s&fileIds=%s"%(model_id,element_id,file_id)
    res = requests.get(url, headers = header1)
    assert res.status_code==200, "获取标准化审核状态失败 文件id %s 元素id %s"%(file_id, element_id)
    review_status = jsonpath(res.json(),"$[0].status")
    if review_status is not False:
        return True if review_status[0]==1 else False



def set_std_review_status(file_id, model_id, element_ids, status, need_notify=False):
    """执行标准化审核操作-元素级别

    :param file_id :文件ID
    :param model_id :模型ID
    :param element_ids : 元素ID
    :param status :1/0 1审核通过、0审核取消
    :param need_notify: 撤销审核时可能需要邮件通知手工测试人员(项目的配置文件中[Email] section的manual_testers项中配置的接收者)，可设置True/False

    :return:
    """

    __init()
    url = url1 + "/api/v1/StdReviewStatus/review-muti-elements"
    header = header2
    payload = {"fileId": file_id,
               "modelId": model_id,
               "status": status,
               "elementIds": element_ids,
               "remark": "test"}
    try:
        res = requests.put(url=url, headers=header, json=payload)
        assert res.status_code == 200, "标准化审核接口响应状态码为%s：%s" % (res.status_code, res.text)
        if need_notify:
            notify("标准化审核状态变更：file %s model %s elements %s has been set to %s"%(file_id, model_id, element_ids, status))
        return res.json()
    except Exception as e:
        logging.error("review_element:%s" % e)
        return None

    # res = requests.put(url=url, headers=header, json=payload)
    # assert res.status_code == 200, "标准化审核接口响应状态码为%s：%s" % (res.status_code, res.text)
    # if need_notify:
    #     notify("标准化审核状态变更：file %s model %s elements %s has been set to %s"%(file_id, model_id, element_ids, status))
    # return res.json()



def get_std_review_status(file_id, model_id, element_id):
    """获取文件元素的标准化审核状态

    :param file_id :文件ID
    :param model_id :模型ID
    :param element_ids : 元素ID

    :return:
    """

    __init()

    url = url1 + "/api/v1/StdReviewStatus/filter/paging?modelId=%s&elementIds=%s&fileIds=%s"%(model_id,element_id,file_id)
    res = requests.get(url, headers = header1)
    assert res.status_code==200, "获取标准化状态失败：file_id %s, model_id %s, element_id %s"%(file_id, model_id, element_id)

    return jsonpath(res.json(), "$.data[0].status")[0]


def review_multi_elements(file_id, model_id, element_ids, status,need_notify=False):


    __init()
    url = url1 + "/api/v1/StdReviewStatus/review-muti-elements"
    header = header2
    payload = {"fileId": file_id,
               "modelId": model_id,
               "status": status,
               "elementIds": element_ids,
               "remark": "test"}
    try:
        res = requests.put(url=url, headers=header, json=payload)
        assert res.status_code == 200, "标准化审核接口响应状态码为%s：%s" % (res.status_code, res.text)
        if need_notify:
            notify(
                "标准化审核状态变更：file %s model %s elements %s has been set to %s" % (file_id, model_id, element_ids, status))
        return res.json()
    except Exception as e:
        logging.error("review_element:%s" % e)
        return 0


def set_std_review_pass(file_id, model_id, element_id, need_notify=False):
    """设置元素标准化审核通过
    当前状态0：调用审核通过接口
    当前状态1：忽略
    当前状态2：先取消审核，再审核通过

    :param file_id :文件ID
    :param model_id :模型ID
    :param element_id : 元素ID
    :param need_notify: 撤销审核时可能需要邮件通知手工测试人员(项目的配置文件中[Email] section的manual_testers项中配置的接收者)，可设置True/False

    :return:
    """

    current_status = get_std_review_status(file_id, model_id, element_id)
    if current_status in [0,2]:
        review_multi_elements(file_id,model_id,[element_id],1,need_notify)
    else: #已审核通过的元素不能通过审核
        pass



def set_std_review_fail(file_id, model_id, element_id, need_notify=False):
    """设置元素标准化审核失败
    当前状态0：撤销审核
    当前状态1：撤销审核
    当前状态2：忽略

    :param file_id :文件ID
    :param model_id :模型ID
    :param element_id : 元素ID
    :param need_notify: 撤销审核时可能需要邮件通知手工测试人员(项目的配置文件中[Email] section的manual_testers项中配置的接收者)，可设置True/False

    :return:
    """
    current_status = get_std_review_status(file_id, model_id, element_id)
    if current_status == 1:
        #fallback review
        pass
    else:  #非审核通过状态不可撤回审核
        pass


def set_std_review_unreview(file_id, model_id, element_id, need_notify=False):
    """设置元素标准化未审核状态
    当前状态0：忽略
    当前状态1：取消审核
    当前状态2：忽略

    :param file_id :文件ID
    :param model_id :模型ID
    :param element_id : 元素ID
    :param need_notify: 撤销审核时可能需要邮件通知手工测试人员(项目的配置文件中[Email] section的manual_testers项中配置的接收者)，可设置True/False

    :return:
    """
    current_status = get_std_review_status(file_id, model_id, element_id)
    if current_status == 1:
        review_multi_elements(file_id, model_id, [element_id], 0, need_notify)
    else:  # 只有审核成功的元素可恢复为未审核状态
        pass

def get_std_review_records(status=0, limit=10):
    """获取指定标准化审核状态的记录

    :param status: 标准化审核状态， 0-未审核 1-审核通过 2-审核失败

    :return: 文件id，模型id， 元素id的组合
    """

    sql = """SELECT s.file_id,s.model_id,s.element_id FROM `t_das_std_review_status` s
                INNER JOIN t_das_model m on m.id=s.model_id
                WHERE m.status in (3,4) AND s.status=%s LIMIT %s;
        """%(status, limit)

    res = execute_query(sql)

    return res


def trigger_re_std(file_id, model_name, element_name, modify_row_index, modify_col_index):
    """验证某些特定情况的标准化，需要重新触发标准化，防止由于标准化数据固化而导致的无效验证
    1. 修改披露数据
    2. 重新结构化
    3. 撤销标准化审核
    4. 重新标准化

    :param file_id: 文件id
    :param model_name: 模型名称
    :param element_name: 元素名称
    :param modify_row_index: 修改的披露数据所在行
    :param modify_col_index: 修改的披露数据所在列

    :return:
    """

    from proj_spec.DAS.extractdata import modify_extract_table_data
    from proj_spec.DAS.model import get_model_id
    from proj_spec.DAS.element import get_element_id
    from proj_spec.DAS.strcut import structurize

    # 修改披露数据确保重新标准化
    if modify_extract_table_data(model_name, element_name, file_id, modify_row_index, modify_col_index,
                                 '1,234,567.89') is False: # 修改为一dummy数据
        return False

    model_id = get_model_id(model_name)
    element_id = get_element_id(model_name, element_name)
    # 触发结构化
    structurize(file_id, model_id, [element_id])

    # 撤销标准化审核后触发标准化
    if is_std_review_passed(file_id, model_id, element_id):
        set_std_review_status(file_id, model_id, [element_id], 0)

    if call_standardize_interface(file_id, model_id, [element_id]):
        return True
    else:
        return False


def get_std_data(file_id, model_id, element_id):
    __init()

    std_res = requests.get(url3+"/api/v1/das/stddata?FileId=%s&ModelId=%s&ElementId=%s"%(file_id, model_id, element_id))
    assert std_res.status_code==200,"获取标准化数据失败: %s"%std_res.text
    return std_res.json()

def get_restate_col_display_names(std_table_name):
    """返回指定标准化表对应的重述列的显示名称

    :param std_table_name: 标准化表名

    :return:
    """


    return [info['display_name'] for info in
     get_std_col_infos(std_field='%rv', is_output=1, tbl_name=std_table_name)]  # 从数据库获取的重述类型字段显示名称列表

#def revoke_std_review(file_id, model_id, element_id):

def wait_for_std_update(file_id, model_id, element_id, start_time, timeout=30):
    """等待指定的标准化数据更新完成，或超时

    :param file_id: 文件id
    :param model_id: 模型id
    :param element_id: 元素id
    :param start_time: 起始时间，作为对比，当标准化数据的更新时间大于start_time时即表示更新完成
    :param timeout: 设定的超时时间，单位为秒，默认30

    :return: 更新完成返回True，超时返回False
    """

    __init()
    url = url3 + "/api/v1/das/stddata?ModelId=%s&ElementId=%s&FileId=%s"%(model_id,element_id,file_id)
    timezone_offset = int(glo.get_value("timezone_offset"))

    upt = start_time - 1
    current_time = start_time

    while upt < start_time and current_time-start_time < timeout:
        res = requests.get(url, headers=header2)
        assert res.status_code == 200, "获取标准化数据失败，file %s model id %s element id %s" % (file_id, model_id, element_id)
        upt_str = res.json()['upt']

        # start_time_str = timestamp_to_str(start_time,'%Y-%m-%dT%H:%M:%S.000Z')
        upt = str_to_timestamp(upt_str, '%Y-%m-%dT%H:%M:%S.000Z') + int(timezone_offset) * 60 * 60
        time.sleep(2)
        current_time = time.time()


def get_fin_item_const_value(fin_item_code):
    """获取常量比率类型科目的常量值

    :param fin_item_code:
    :return:
    """
    sql = """SELECT const_value FROM t_das_std_fin_item WHERE item_type=3 and std_code='%s'"""%fin_item_code

    res = execute_query(sql)

    if len(res)==0:
        logging.error("字段不是常量比率类型：%s")%fin_item_code
    else:
        return res[0][0]



def validate_std_data(file_id, model_name, element_name, expected_datafrm, start_time):
    """验证标准化数据

    :param file_id: 文件ID
    :param model_name: 模型名称
    :param element_name: 元素名称
    :param expected_datafrm: 预期结果的DataFrame
    :param start_time: 执行标准化的起始时间（获取标准化结果时需确保标准化数据的更新时间是在start_time之后）

    :return:

    """
    from proj_spec.DAS.model import get_model_id
    from proj_spec.DAS.element import get_element_id
    import pandas as pd
    #显示所有行列
    pd.set_option('display.max_columns',None)
    pd.set_option('display.max_rows', None)

    model_id = get_model_id(model_name)
    element_id = get_element_id(model_name, element_name)
    timezone_offset = glo.get_value("timezone_offset")
    upt = start_time-1
    std_result = {}
    wait_time = 0
    while upt < start_time and wait_time<10:
        time.sleep(2)
        wait_time = wait_time+2
        std_result = get_std_data(file_id, model_id, element_id)
        upt = str_to_timestamp(std_result['upt'], '%Y-%m-%dT%H:%M:%S.000Z') + int(timezone_offset) * 60 * 60

    header_names = std_result['headerNames'] #标准化数据接口中的列名
    columns = list(expected_datafrm.columns) #预期结果中的列名

    unit_colname_mapping = {"原始单位":"originalUnit", "标准化单位":"stdUnit"}

    for first_col in expected_datafrm.ix[:,0]: # 按行对首列内容进行迭代
        expected_filtered = (expected_datafrm[expected_datafrm[columns[0]] == first_col]).values.tolist()
        std_data = jsonpath(std_result, '$.cellData[?(@[0]["cellValue"]=="%s")]' % first_col)

        for i,record in enumerate(std_data):

            #for col in [col for col in columns if col not in ['原始单位','标准化单位']]:
            for j,col in enumerate(columns):
                if '原始单位' in col or '标准化单位' in col:
                    if '.' in col:  # 预期数据中单位列列名会被读取为'原始单位.1'的格式
                        col_key = col[:col.index('.')]
                    else:
                        col_key = col
                    target_header_name = columns[columns.index(col)-1-['原始单位','标准化单位'].index(col_key)]
                    if expected_filtered[i][j]=='':
                        assert unit_colname_mapping[col_key] not in std_data[i][header_names.index(target_header_name)] or \
                               std_data[i][header_names.index(target_header_name)][unit_colname_mapping[col_key]]=='', "%s %s行%s列 - 预期为空, 实际： %s" \
                                %(first_col, i, j, std_data[i][header_names.index(target_header_name)][unit_colname_mapping[col_key]])
                    else:
                        assert std_data[i][header_names.index(target_header_name)][unit_colname_mapping[col_key]]==expected_filtered[i][j], \
                        "%s %s行%s列 - 预期：%s, 实际： %s"%(first_col, i, j, expected_filtered[i][j], std_data[i][header_names.index(target_header_name)][unit_colname_mapping[col_key]])
                elif type(expected_filtered[i][j]) is str:
                    if expected_filtered[i][j]=='':
                        assert 'cellValue' not in std_data[i][header_names.index(col)]  or std_data[i][header_names.index(col)]['cellValue']=='', \
                        "%s %s行%s列 - 预期为空，实际：%s"%(first_col, i, j, std_data[i][header_names.index(col)]['cellValue'])
                    else:
                        assert std_data[i][header_names.index(col)]['cellValue'] == expected_filtered[i][j], \
                            "%s %s行%s列 - 预期： %s， 实际: %s"%(first_col, i, j, expected_filtered[i][j], std_data[i][header_names.index(col)]['cellValue'])
                elif type(expected_filtered[i][j]) is pd.Timestamp:
                    assert std_data[i][header_names.index(col)]['cellValue'] == (str(expected_filtered[i][j])).split()[0], \
                            "%s %s行%s列 - 预期： %s， 实际: %s"%(first_col, i, j, (str(expected_filtered[i][j])).split()[0], std_data[i][header_names.index(col)]['cellValue'])
                else:
                    assert float(get_number_from_string(std_data[i][header_names.index(col)]['cellValue'])) == float(expected_filtered[i][j]), \
                        "%s %s行%s列 - 预期： %s， 实际: %s"%(first_col, i, j, float(get_number_from_string(std_data[i][header_names.index(col)]['cellValue'])),
                                                   float(get_number_from_string(std_data[i][header_names.index(col)]['cellValue'])))

    return True


def set_std_review_status_initial(file_id, model_id, element_id):
    """
    设置文件元素的审核状态为初次审核：审核状态为0，并且init_review_time和init_reviewer字段为null
    """
    sql = f'update t_das_std_review_status set status=0, init_review_time=null, init_reviewer=null where model_id={model_id} and element_id={element_id} and file_id={file_id}'
    try:
        execute_query(sql)
        execute_query("commit;")
    except Exception as e:
        logging.error(e)